package com.cpi.cornpp.instructionspurchases.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.instructionsforecasts.exception.InstructionsForecastsException;
import com.cpi.cornpp.instructionspurchaseforecast.dto.InstructionPurchaseForecastDTO;
import com.cpi.cornpp.instructionspurchaseforecast.dto.InstructionPurchaseForecastSearchDTO;
import com.cpi.cornpp.instructionspurchases.dto.InstructionPurchaseDTO;
import com.cpi.cornpp.instructionspurchases.dto.InstructionPurchaseSearchDTO;
import com.cpi.cornpp.instructionspurchases.exception.InstructionPurchaseException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
import com.cs.common.utils.NumberUtil;
import com.cs.common.utils.StringUtils;

public class InstructionPurchaseDAO extends DBDAO {	
	/**
	 * SQL para mostrar la lista de instrucciones de compra
	 * 	select 	pih.purchase_instruction_id,
						pih.purchase_date,
						pih.comments,
						pih.status,
						pid.purchase_instruction_detail_id,
						pid.row,
						pid.customer_id,
						cus.description,
						pid.item_id,
						ite.description,
						pid.item_quantity,
						pid.corn_quantity,
						(
				            select  sum(quantity)  
				            from    purchases_instructions_futures 
				            where   purchase_instruction_detail_id = pid.purchase_instruction_detail_id
				        ) as contratos_a_comprar,
				        (
				            select  isnull( sum(cf.quantity),0) 
				            from    purchases_instructions_futures pif 
				                        inner join realizeds_instructions ri 
				                        on pif.purchase_instruction_future_id = ri.purchase_instruction_future_id 
				                        inner join corn_futures cf 
				                        on cf.corn_future_id = ri.corn_future_id
				            where   pif.purchase_instruction_detail_id  = pid.purchase_instruction_detail_id
				        ) as contratos_comprados,
				        isnull( ( 
				                select  sum(quantity)  
				                from    purchases_instructions_futures 
				                where   purchase_instruction_detail_id = pid.purchase_instruction_detail_id
				            ),0
				        ) - 
				        (
				            select  isnull( sum(cf.quantity),0) 
				            from    purchases_instructions_futures pif 
				                        inner join realizeds_instructions ri 
				                        on pif.purchase_instruction_future_id = ri.purchase_instruction_future_id 
				                        inner join corn_futures cf 
				                        on cf.corn_future_id = ri.corn_future_id
				            where   pif.purchase_instruction_detail_id  = pid.purchase_instruction_detail_id
				        ) as saldo,
						pid.future_type_id,
						pid.instruction_strategy_id,
						pid.instruction_type_id,
						pid.status
		from 	purchases_instructions pih
						left join purchases_instructions_details pid
						on pih.purchase_instruction_id = pid.purchase_instruction_id
						left join vw_items ite
						on pid.item_id = ite.item_id
						left join vw_customers cus
						on pid.customer_id = cus.customer_id
		where substring( '00000', 0, 6-len( pih.purchase_instruction_id ) ) + convert( varchar(5),  pih.purchase_instruction_id ) like ? and
					( pid.customer_id like ? or cus.description like ? ) and
					( pid.item_id like ? or ite.description like ? )
					and pih.status = ?
					and pid.status = ?
	 */
	private String SQL_INSTRUCTION_LIST = "select pih.purchase_instruction_id, pih.purchase_date, pih.comments, pih.status, pid.purchase_instruction_detail_id, pid.row, pid.customer_id, cus.description, pid.item_quantity, pid.corn_quantity,(select sum(quantity) from purchases_instructions_futures where purchase_instruction_detail_id = pid.purchase_instruction_detail_id) as contratos_a_comprar,(select  isnull( sum(cf.quantity),0) from purchases_instructions_futures pif inner join realizeds_instructions ri on pif.purchase_instruction_future_id = ri.purchase_instruction_future_id inner join corn_futures cf on cf.corn_future_id = ri.corn_future_id where   pif.purchase_instruction_detail_id  = pid.purchase_instruction_detail_id) as contratos_comprados,isnull( ( select  sum(quantity) from purchases_instructions_futures where   purchase_instruction_detail_id = pid.purchase_instruction_detail_id),0) - (select  isnull( sum(cf.quantity),0) from purchases_instructions_futures pif inner join realizeds_instructions ri on pif.purchase_instruction_future_id = ri.purchase_instruction_future_id inner join corn_futures cf on cf.corn_future_id = ri.corn_future_id where   pif.purchase_instruction_detail_id  = pid.purchase_instruction_detail_id) as saldo,pid.future_type_id, pid.instruction_strategy_id, pid.instruction_type_id, pid.status, (select count(*) from purchases_instructions_items_details where purchase_instruction_detail_id = pid.purchase_instruction_detail_id) as total_productos from purchases_instructions pih left join purchases_instructions_details pid on pih.purchase_instruction_id = pid.purchase_instruction_id left join vw_customers cus on pid.customer_id = cus.customer_id where substring( '00000', 0, 6-len( pih.purchase_instruction_id ) ) + convert( varchar(5),  pih.purchase_instruction_id ) like ?  ";//$NON-NLS-1$
	/** Insertar una nueva instrucion de compra*/
	private String SQL_INSERT_PURCHASE_INSTRUCTION ="INSERT INTO PURCHASES_INSTRUCTIONS (PURCHASE_DATE, COMMENTS, STATUS) VALUES (?,?,?)" ;//$NON-NLS-1$
	/** Actualizar una instrucion de compra*/
	private String SQL_UPDATE_PURCHASE_INSTRUCTION ="UPDATE PURCHASES_INSTRUCTIONS SET PURCHASE_DATE = ? , COMMENTS=?, STATUS=? WHERE PURCHASE_INSTRUCTION_ID = ?" ;//$NON-NLS-1$
	private String SQL_INSTRUCTION_STATUS = " and pih.status = ? ";//$NON-NLS-1$
	private String SQL_INSTRUCTION_STATUS_WITHOUT_PARAM = " and pih.status = "; //$NON-NLS-1$
	private String SQL_INSTRUCTION_DETAIL_STATUS = " and pid.status = ? ";//$NON-NLS-1$
	private String SQL_INSTRUCTION_DETAIL_STATUS_WITHOUT_PARAM = " and pid.status = "; //$NON-NLS-1$
	private String SQL_INSTRUCTION_CUSTOMER = " and ( pid.customer_id like ? or cus.description like ? ) ";//$NON-NLS-1$
	/**
	 * 		SELECT  ys.year, 
			        ys.customer_id, 
			        ys.item_id, 
			        contracted, 
			        non_contracted, 
			        corn_contracted, 
			        non_corn_contracted, 
			        cus.description, 
			        it.description, 
			        yf.instructions_futures, 
			        (((yf.instructions_futures/(select qty from units where unit_id = 'CON'))/corn_contracted)*100) 
			FROM    vw_yearly_sales_forecasts_factors ys  
								inner join vw_items it
					            on ys.item_id = it.item_id
					            inner join vw_customers cus
					            on ys.customer_id = cus.customer_id
					            LEFT OUTER JOIN vw_yearly_futures_customers_items yf 
					            ON  ys.year = yf.year AND 
					                ys.customer_id = yf.customer_id AND 
					                ys.item_id = yf.item_id 
			WHERE   	(yf.customer_description like ? or ys.customer_id like ?) AND 
			        		(yf.item_description like ? or ys.item_id like ?) AND 
			        		ys.year between ? and ? 
	 */
	private static final String SQL_INSTRUCTION_PURCHASE_FORECAST_LIST = " EXEC sp_global_instructions_forecasts_browse ?, ?, ?, ?, ?, ?, ? ";
	private static final String SQL_GET_INSTRUCTION_PURCHASE_BY_ID = "select purchase_instruction_id, purchase_date, comments, status from purchases_instructions where purchase_instruction_id = ?";//$NON-NLS-1$	
	private static final String INSTRUCTION_PURCHASE_STATUS_OPENED_ID = "INSTRUCTION_PURCHASE_STATUS_OPENED_ID";//$NON-NLS-1$
	private static final String INSTRUCTION_PURCHASE_DETAIL_STATUS_OPENED_ID = "INSTRUCTION_PURCHASE_DETAIL_STATUS_OPENED_ID";//$NON-NLS-1$
	private static final String SQL_GET_INSTRUCTION_PURCHASE_FORECAST_DETAIL = "EXEC sp_global_instructions_forecasts_detail ?, ?, ?";//$NON-NLS-1$
	
	private static final String SQL_IS_ACTIVE_INSTRUCTION = "select count(*) from purchases_instructions_details pid where pid.purchase_instruction_id = (select purchase_instruction_id from purchases_instructions_details where purchase_instruction_detail_id = ?) and status = ?";
	private static final String SQL_CHANTE_STATUS = "update purchases_instructions set status = ? where purchase_instruction_id = (select purchase_instruction_id from purchases_instructions_details where purchase_instruction_detail_id = ?) ";
	
	InstructionPurchaseDTO instructionPurchaseDTO = null;
	/**
	 * Agregar una  nueva instruccion de compra
	 * @return boolean TRUE -> Se dio de alta correctamente
	 * 				   FALSE -> No se dio de alta
	 * @throws InstructionsForecastsException 
	 */
	public int insertNewPurchaseInstruction(InstructionPurchaseDTO ipDTO)throws InstructionPurchaseException, InstructionsForecastsException{
		PreparedStatement preparedStatement =  null;		
		int purchaseInstructionId =0; 
		try {			
			preparedStatement =  prepareSQL( SQL_INSERT_PURCHASE_INSTRUCTION , dsName);			
			preparedStatement.setDate(1, ipDTO.getDate());
			preparedStatement.setString(2, ipDTO.getComments());
			preparedStatement.setInt(3, ipDTO.getPurchaseStatus());
			executeInsert(preparedStatement);
			purchaseInstructionId = NumberUtil.parseInt(getInsertedId(preparedStatement.getConnection()));
		}catch (PoolException poe) {
			InstructionsForecastsException ife = new InstructionsForecastsException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;
		} catch (SQLException sqle){
			InstructionsForecastsException ife = new InstructionsForecastsException(sqle, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ife;
		} finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqle){
			}
		}
		return purchaseInstructionId;			
	}
	/**
	 * Metodo que obtiene una Instruccion de Compra
	 * 
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws InstructionPurchaseException
	 */
	public InstructionPurchaseDTO getInstructionPurchaseDTO(int id) throws InstructionPurchaseException{
		PreparedStatement preparedStatement =  null;
		InstructionPurchaseDTO dto = null;
		try {
			preparedStatement =  prepareSQL(SQL_GET_INSTRUCTION_PURCHASE_BY_ID, dsName);						
			preparedStatement.setInt(1, id);									
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {	
				dto = new InstructionPurchaseDTO();
				dto.setId(rst.getInt(1));
				dto.setDate(rst.getDate(2));
				dto.setComments(rst.getString(3));
				dto.setPurchaseStatus(rst.getInt(4));
			}
		}catch (PoolException poe) {
			InstructionPurchaseException ife = new InstructionPurchaseException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;	
		}catch (SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return dto;
	}
	
	/**
	 * Actualziar una instruccion de compra
	 * @return boolean TRUE -> Se dio de alta correctamente
	 * 				   FALSE -> No se dio de alta
	 * @throws InstructionsForecastsException 
	 */
	public boolean updateNewPurchaseInstruction(InstructionPurchaseDTO ipDTO)throws InstructionPurchaseException, InstructionsForecastsException{
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		boolean result =false; 
		try {			
			preparedStatement =  prepareSQL( SQL_UPDATE_PURCHASE_INSTRUCTION , dsName);			
			preparedStatement.setDate(1, ipDTO.getDate());
			preparedStatement.setString(2, ipDTO.getComments());
			preparedStatement.setInt(3, ipDTO.getPurchaseStatus());
			preparedStatement.setInt(4, ipDTO.getId());
			exInsert = executeInsert(preparedStatement);
			
			if (exInsert <= 0 ) {
				InstructionsForecastsException instructionsForecastsException = null;
				instructionsForecastsException = new InstructionsForecastsException (MSG_NO_INSERT,InstructionsForecastsException.LAYER_DAO,InstructionsForecastsException.ACTION_INSERT);				
				throw instructionsForecastsException;				
			}
			else{
				result=true;
			}
		}catch (PoolException poe) {
			InstructionsForecastsException ife = new InstructionsForecastsException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;
		} catch (SQLException sqle){
			InstructionsForecastsException ife = new InstructionsForecastsException(sqle, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ife;
		} finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqle){
			}
		}
		return result;			
	}	
	
	public List list()throws InstructionPurchaseException{
		List instructionsDTOs = null;
		InstructionPurchaseDTO instructionPurchaseDTO = null;		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			instructionsDTOs = new ArrayList(0);
			pstm = prepareSQL(SQL_INSTRUCTION_LIST, dsName);
			rs = executeQuery(pstm);
			while (rs.next()){
				instructionPurchaseDTO = new InstructionPurchaseDTO();
				
				instructionsDTOs.add(instructionPurchaseDTO);
			}
		}catch (PoolException poolException){
			InstructionPurchaseException instructionPurchaseException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		} catch(SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return instructionsDTOs;
	}
	
	public List list(InstructionPurchaseSearchDTO instructionPurchaseSearchDTO, int orderField, boolean ascendingOrder)throws InstructionPurchaseException{
		List instructionsDTOs = null;
		InstructionPurchaseDTO instructionPurchaseDTO = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		int posValue;
		try{
			sql = new StringBuffer(0);
			instructionsDTOs = new ArrayList(0);
			posValue = 1;
			sql.append(SQL_INSTRUCTION_LIST);
			if( instructionPurchaseSearchDTO.getCustomer() != null && !instructionPurchaseSearchDTO.getCustomer().trim().equals(StringUtils.EMPTY_STRING)){
				sql.append(SQL_INSTRUCTION_CUSTOMER);
			}
			if( instructionPurchaseSearchDTO.getBalance() != null && !instructionPurchaseSearchDTO.getBalance().trim().equals(StringUtils.EMPTY_STRING) ){
				sql.append(SQL_INSTRUCTION_STATUS_WITHOUT_PARAM);
				sql.append(SystemParameterCollection.getInstance().getSystemParameterBean(INSTRUCTION_PURCHASE_STATUS_OPENED_ID).getValue());
				sql.append(SQL_INSTRUCTION_DETAIL_STATUS_WITHOUT_PARAM);
				sql.append(SystemParameterCollection.getInstance().getSystemParameterBean(INSTRUCTION_PURCHASE_DETAIL_STATUS_OPENED_ID).getValue());
				sql.append(DBDAO.SQL_AND);
				sql.append(InstructionPurchaseSearchDTO.BALANCE_FIELD);
				sql.append(instructionPurchaseSearchDTO.getBalance());
			}
			if( instructionPurchaseSearchDTO.getPurchaseStatus() > 0 ){
				sql.append(SQL_INSTRUCTION_STATUS);
			}
			if( instructionPurchaseSearchDTO.getPurchaseDetailStatus() > 0 ){
				sql.append(SQL_INSTRUCTION_DETAIL_STATUS);
			}
			sql.append(SQL_ORDER_BY);
			switch (orderField){
				case 1: sql.append(InstructionPurchaseSearchDTO.REFERENCE_ORDER);
							if (ascendingOrder)	{
								sql.append(SQL_ASCENDING_ORDER);
							} else {
								sql.append(SQL_DESCENDING_ORDER);
							}
							sql.append(DBDAO.SQL_COMMA);
							sql.append(InstructionPurchaseSearchDTO.LINE_ORDER);
					break;
				case 2: sql.append(InstructionPurchaseSearchDTO.STATUS_ORDER);
					break;
			}
			if (ascendingOrder)	{
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setString(posValue++, StringUtils.likeStatement(instructionPurchaseSearchDTO.getReference()));
			if( instructionPurchaseSearchDTO.getCustomer() != null && !instructionPurchaseSearchDTO.getCustomer().trim().equals(StringUtils.EMPTY_STRING)){
				pstm.setString(posValue++, StringUtils.likeStatement(instructionPurchaseSearchDTO.getCustomer()));
				pstm.setString(posValue++, StringUtils.likeStatement(instructionPurchaseSearchDTO.getCustomer()));
			}
			if( instructionPurchaseSearchDTO.getItem() != null && !instructionPurchaseSearchDTO.getItem().trim().equals(StringUtils.EMPTY_STRING)){
				pstm.setString(posValue++, StringUtils.likeStatement(instructionPurchaseSearchDTO.getItem()));
				pstm.setString(posValue++, StringUtils.likeStatement(instructionPurchaseSearchDTO.getItem()));
			}
			if( instructionPurchaseSearchDTO.getPurchaseStatus() > 0 ){
				pstm.setInt(posValue++, instructionPurchaseSearchDTO.getPurchaseStatus());
			}
			if( instructionPurchaseSearchDTO.getPurchaseDetailStatus() > 0 ){
				pstm.setInt(posValue++, instructionPurchaseSearchDTO.getPurchaseDetailStatus());
			}
			rs = executeQuery(pstm);
			while (rs.next()){
				instructionPurchaseDTO = new InstructionPurchaseDTO();
				instructionPurchaseDTO.setId(rs.getInt(1));
				instructionPurchaseDTO.setDate(rs.getDate(2));
				instructionPurchaseDTO.setComments(rs.getString(3));
				instructionPurchaseDTO.setPurchaseStatus(rs.getInt(4));
				instructionPurchaseDTO.setPurchaseDetailId(rs.getInt(5));
				instructionPurchaseDTO.setRow(rs.getInt(6));
				instructionPurchaseDTO.setCustomerId(rs.getString(7));
				instructionPurchaseDTO.setCustomerName(rs.getString(8));
				//instructionPurchaseDTO.setItemId(rs.getString(9));
				//instructionPurchaseDTO.setItemName(rs.getString(10));
				instructionPurchaseDTO.setItemQuantity(rs.getDouble(9));
				instructionPurchaseDTO.setCornQuantity(rs.getDouble(10));
				instructionPurchaseDTO.setContractQuantity(rs.getDouble(11));
				instructionPurchaseDTO.setContractQuantityPurchased(rs.getDouble(12));
				instructionPurchaseDTO.setContractBalance(rs.getDouble(13));
				instructionPurchaseDTO.setFuturesType(rs.getString(14));
				instructionPurchaseDTO.setStrategy(rs.getInt(15));
				instructionPurchaseDTO.setType(rs.getInt(16));
				instructionPurchaseDTO.setPurchaseDetailStatus(rs.getInt(17));
				instructionPurchaseDTO.setItemCount(rs.getInt(18));
				instructionsDTOs.add(instructionPurchaseDTO);
			}
		}catch (PoolException poolException){
			InstructionPurchaseException instructionPurchaseException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		} catch(SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return instructionsDTOs;
	}
	
	/**
	 * 
	 * @param searchDto
	 * @param orderField
	 * @param ascendingOrder
	 * @return
	 * @throws InstructionPurchaseException
	 */
	public List listInstructionPurchaseForecasts(InstructionPurchaseForecastSearchDTO searchDto, int orderField, boolean ascendingOrder)throws InstructionPurchaseException{
		List instructionsDTOs = null;
		InstructionPurchaseForecastDTO dto = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		String customerFilter = null;
		String customerRightFilter = null;
		String itemFilter = null;
		try{
			sql = new StringBuffer(0);
			instructionsDTOs = new ArrayList(0);
			sql.append(SQL_INSTRUCTION_PURCHASE_FORECAST_LIST);
			pstm = prepareSQL(sql.toString(), dsName);
			if( !org.apache.commons.lang.StringUtils.isEmpty(searchDto.getCustomerDescription()) ){
				customerFilter = StringUtils.likeStatement(searchDto.getCustomerDescription());
				customerRightFilter = StringUtils.likeRightStatement(searchDto.getCustomerDescription());
			}else{
				customerFilter = StringUtils.likeStatement(StringUtils.EMPTY_STRING);
				customerRightFilter = StringUtils.likeRightStatement(StringUtils.EMPTY_STRING);
			}
			if( !org.apache.commons.lang.StringUtils.isEmpty(searchDto.getItemDescription()) ){
				itemFilter = StringUtils.likeStatement(searchDto.getItemDescription());
			}else{
				itemFilter = StringUtils.likeStatement(StringUtils.EMPTY_STRING);
			}
			pstm.setString(1, customerFilter);
			pstm.setString(2, customerRightFilter);
			pstm.setString(3, itemFilter);
			pstm.setDouble(4, searchDto.getCoverageInit());
			pstm.setDouble(5, searchDto.getCoverageEnd());
			pstm.setInt(6, searchDto.getFromYear());
			pstm.setInt(7, searchDto.getToYear());
			rs = executeQuery(pstm);
			while (rs.next()){
				dto = new InstructionPurchaseForecastDTO();
				dto.setYear(rs.getInt(1));
				dto.setCustomerId(rs.getString(2));
				dto.setItemId(rs.getString(4));
				dto.setContracted(rs.getDouble(6));
				dto.setNonContracted(rs.getDouble(7));
				dto.setCornContracted(rs.getDouble(8));
				dto.setNonCornContracted(rs.getDouble(9));
				dto.setCustomerDescription(rs.getString(3));
				dto.setItemDescription(rs.getString(5));
				dto.setInstructionFutures(rs.getInt(10));
				dto.setCoverage(rs.getDouble(11));
				instructionsDTOs.add(dto);
			}
		}catch (PoolException poolException){
			InstructionPurchaseException instructionPurchaseException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		} catch(SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return instructionsDTOs;
	}
	/**
	 * Metodo que regresa una lista de
	 * Detalle Pronostico de Ventas
	 * 
	 * @author Edgar Joao
	 * @param periodId
	 * @param customerId
	 * @param productId
	 * @return List of InstructionPurchaseForecastView
	 * @throws InstructionPurchaseException
	 */
	public List getInstructionForecastDetail(int periodId, String customerId, String productId) throws InstructionPurchaseException{
		List list = null;
		InstructionPurchaseForecastDTO dto = null; 
		PreparedStatement preparedStatement =  null;		
		try {
			list = new ArrayList(0);
			preparedStatement =  prepareSQL(SQL_GET_INSTRUCTION_PURCHASE_FORECAST_DETAIL, dsName);						
			preparedStatement.setString(1, customerId);
			preparedStatement.setString(2, productId);
			preparedStatement.setInt(3, periodId);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()) {	
				dto = new InstructionPurchaseForecastDTO();
				dto.setYear(rst.getInt(1));
				dto.setMonth(rst.getInt(2));
				dto.setCustomerId(rst.getString(3));
				dto.setItemId(rst.getString(4));
				dto.setContracted(rst.getDouble(5));
				dto.setNonContracted(rst.getDouble(6));
				dto.setCornContracted(rst.getDouble(7));
				dto.setNonCornContracted(rst.getDouble(8));
				dto.setCustomerDescription(rst.getString(9));
				dto.setItemDescription(rst.getString(10));
				dto.setInstructionFutures(rst.getInt(11));
				dto.setCoverage(rst.getDouble(12));
				list.add(dto);
			}
		}catch (PoolException poe) {
			InstructionPurchaseException ife = new InstructionPurchaseException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;	
		}catch (SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return list;
	}
	
	public boolean isInstructionFinished(int purchaseInstructionDetailId, int statusActive) throws InstructionPurchaseException{
		boolean instructionFinished = false;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet;
		try{
			preparedStatement = prepareSQL(SQL_IS_ACTIVE_INSTRUCTION,dsName);
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			preparedStatement.setInt(2, statusActive);
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				instructionFinished = resultSet.getInt(1) == 0;
			}
		}catch (PoolException poe) {
			InstructionPurchaseException ife = new InstructionPurchaseException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;	
		}catch (SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return instructionFinished;
	}
	
	public void changeStatus(int purchaseInstructionDetailId, int status) throws InstructionPurchaseException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_CHANTE_STATUS, dsName);
			preparedStatement.setInt(1, status);
			preparedStatement.setInt(2, purchaseInstructionDetailId);
			executeUpdate(preparedStatement);
		}catch (PoolException poe) {
			InstructionPurchaseException ife = new InstructionPurchaseException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;	
		}catch (SQLException sqlException){
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw instructionPurchaseException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
}